<?php

class Institution_selection_model extends CI_Model {

    function __construct() {
        parent::__construct(); {
            $this->load->helper('security');
        }
    }

    function getSelectionList($institutionList, $countryID) {
        $data = array(
            'all' => 'All',
        );
        if ($institutionList == "InsType") {
            $query = $this->db->order_by("institution_type_desc", "asc");
            $query = $this->db->get('institution_type');
            foreach ($query->result_array() as $row) {
                $data[$row['institution_type_id']] = $row['institution_type_desc'];
            }
            return $data;
        } else if ($institutionList == "InsName") {
            if ($countryID == 'countrynull') {
                $query = $this->db->order_by("institution_name", "asc");
                $query = $this->db->get('institution');
                foreach ($query->result_array() as $row) {
                    $data[$row['institution_id']] = $row['institution_name'];
                }
                return $data;
            } else {
                $query = $this->db->query("SELECT * FROM institution WHERE country_id =$countryID ORDER BY institution_name ASC");
                foreach ($query->result_array() as $row) {
                    $data[$row['institution_id']] = $row['institution_name'];
                }
                return $data;
            }
        } else if ($institutionList == "Discipline") {
            $query = $this->db->order_by("discipline_name", "asc");
            $query = $this->db->get('discipline');
            foreach ($query->result_array() as $row) {
                $data[$row['discipline_id']] = $row['discipline_name'];
            }
            return $data;
        } else {
            $none[0] = "None";
            return $none;
        }
    }

    function entry_remove() {
        $id = $this->input->post('institution_id');
        foreach ($id as $ids) {
            $query = $this->db->query("DELETE FROM institution_has_student WHERE institution_id=$ids AND student_id=" . $user_id);
        }
        return $query;
    }

    function getSearchResult() {
        $institutionList = $this->input->post('EduInstitution');
        $selectedvalue = $this->input->post('selectedvalue');
        $countryid = $this->input->post('country');
        if ($countryid != "countrynull") {
            if ($selectedvalue == "all") {
                $query = $this->db->query("SELECT *,CONCAT(street_name_1, ' ', street_name_2, ' ', unit_num, ' ', postal_code) AS institution_address FROM institution i, institution_type it, country c WHERE i.institution_type_id = it.institution_type_id AND i.country_id = c.country_id AND i.country_id =" . $countryid);
                return $query->result();
            } else if ($institutionList == "cat") {
                $query = $this->db->query("SELECT *,CONCAT(street_name_1, ' ', street_name_2, ' ', unit_num, ' ', postal_code) AS institution_address FROM institution i, institution_type it, country c WHERE i.institution_type_id = it.institution_type_id AND i.country_id = c.country_id AND i.country_id =" . $countryid);
                return $query->result();
            } else {
                if ($institutionList == "InsType") {
                    $query = $this->db->query("SELECT *,CONCAT(street_name_1, ' ', street_name_2, ' ', unit_num, ' ', postal_code) AS institution_address FROM institution i, institution_type it, country c WHERE i.institution_type_id = it.institution_type_id AND i.country_id = c.country_id AND i.institution_type_id =" . $selectedvalue . " AND i.country_id =" . $countryid);
                    return $query->result();
                } else if ($institutionList == "InsName") {
                    $query = $this->db->query("SELECT *,CONCAT(street_name_1, ' ', street_name_2, ' ', unit_num, ' ', postal_code) AS institution_address FROM institution i, institution_type it, country c WHERE i.institution_type_id = it.institution_type_id AND i.country_id = c.country_id AND  i.institution_id =" . $selectedvalue . " AND i.country_id =" . $countryid);
                    return $query->result();
                } else if ($institutionList == "Discipline") {
                    $query = $this->db->query("SELECT *,CONCAT(street_name_1, ' ', street_name_2, ' ', unit_num, ' ', postal_code) AS institution_address
                            FROM institution i, institution_type it, country c, discipline d, course co, course_has_institution cs
                            WHERE i.institution_type_id = it.institution_type_id
                            AND i.country_id = c.country_id
                            AND d.discipline_id = co.discipline_id
                            AND co.course_id = cs.course_id
                            AND  d.discipline_id =" . $selectedvalue . " AND i.country_id =" . $countryid);
                    return $query->result();
                }
            }
        } else {
            if ($selectedvalue == "all") {
                $query = $this->db->query("SELECT *,CONCAT(street_name_1, ' ', street_name_2, ' ', unit_num, ' ', postal_code) AS institution_address FROM institution i, institution_type it, country c WHERE i.institution_type_id = it.institution_type_id AND i.country_id = c.country_id");
                return $query->result();
            } else {
                if ($institutionList == "InsType") {
                    $query = $this->db->query("SELECT *,CONCAT(street_name_1, ' ', street_name_2, ' ', unit_num, ' ', postal_code) AS institution_address FROM institution i, institution_type it, country c WHERE i.institution_type_id = it.institution_type_id AND i.country_id = c.country_id AND i.institution_type_id =" . $selectedvalue);
                    return $query->result();
                } else if ($institutionList == "InsName") {
                    $query = $this->db->query("SELECT *,CONCAT(street_name_1, ' ', street_name_2, ' ', unit_num, ' ', postal_code) AS institution_address FROM institution i, institution_type it, country c WHERE i.institution_type_id = it.institution_type_id AND i.country_id = c.country_id AND  i.institution_id =" . $selectedvalue);
                    return $query->result();
                } else if ($institutionList == "Discipline") {
                    $query = $this->db->query("SELECT *,CONCAT(street_name_1, ' ', street_name_2, ' ', unit_num, ' ', postal_code) AS institution_address
                            FROM institution i, institution_type it, discipline d, course co, course_has_institution cs
                            WHERE i.institution_type_id = it.institution_type_id
                            AND d.discipline_id = co.discipline_id
                            AND co.course_id = cs.course_id
                            AND  d.discipline_id =" . $selectedvalue);
                    return $query->result();
                }
            }
        }
    }

//    function selInstitutionModel() {
//        $ids = $this->input->post('institution_id');
//        $sql1 = array();
//        foreach ($ids as $id) {
//            $query = $this->db->query("SELECT * FROM institution WHERE institution_id =$id");
//            $query = $query->result();
//            foreach ($query as $sql) {
//                $query1 = array($sql->institution_name, $sql->institution_id);
//                array_unshift($sql1, $query1);
//            }
//        }
//        return $sql1;
////        }
//    }

    function selInstitutionModel($user_id) {
        $institution_id = $this->input->post("institution_id");
        $len = count($institution_id);
        $sql = "SELECT * FROM institution_has_student WHERE student_id=" . $user_id;
        $result = $this->db->query($sql);
        $len1 = $result->num_rows();
        $total = $len1 + $len;
        if ($total > 3) {
            echo "You can only select THREE institutions";
        } else {
            if (count($result->result() == 0)) {
                foreach ($institution_id as $id) {
                    $this->db->query("INSERT INTO institution_has_student(institution_id,student_id) VALUES('" . $id . "','" . $user_id . "')");
                }
                return true;
            }else {
                foreach ($result->result() as $row) {
                    foreach ($institution_id as $id) {
                        if ($row->institution_id == $id) {
                            $break = true;
                            break;
                        } else {
                            $this->db->query("INSERT INTO institution_has_student(institution_id,student_id) VALUES('" . $id . "','" . $user_id . "')");
                            return true;
                        }
                    }
                    if ($break) {
                        break;
                    }
                }
            }
        }
    }

//    function confirmSelectionModel($id) {
//        $sql = $this->db->query("SELECT * FROM institution_has_student WHERE institution_id =$id AND student_id =" . $_SESSION['user_id']);
//        if (count($sql->result()) == 0) {
//            $query = $this->db->query("INSERT INTO institution_has_student(institution_id,student_id) VALUES('" . $id . "','" . $_SESSION['user_id'] . "')");
//            return $query;
//        }
//    }
//
//    function yourSelectedIns() {
//        if (isset($_SESSION['user_id'])) {
//            $sql = "SELECT *
//                FROM institution i, institution_has_student si, student s
//                WHERE i.institution_id = si.institution_id
//                AND si.student_id = s.student_id
//                AND si.student_id=" . $_SESSION['user_id'] . "
//                ORDER BY i.institution_id DESC LIMIT 0,5";
//            $query = $this->db->query($sql);
//            return $query->result();
//        }
//    }
//
//    function yourSelectedCourse() {
//        if (isset($_SESSION['user_id'])) {
//            $sql = "SELECT *
//                FROM course c, course_has_student sc
//                WHERE c.course_id = sc.course_id
//                AND sc.student_id=" . $_SESSION['user_id'] . "
//                ORDER BY sc.course_id DESC";
//            $query = $this->db->query($sql);
//            return $query->result();
//        }
//    }
//
    function showDesc($institution_id) {
        $query = $this->db->query("SELECT institution_description FROM institution WHERE institution_id=$institution_id");
        return $query->result();
    }

    function courseInsModel($user_id) {
        $query = $this->db->query("SELECT *,CONCAT(street_name_1, ' ', street_name_2, ' ', unit_num, ' ', postal_code) AS institution_address
                                            FROM institution i, course c, course_has_institution ci, course_has_student cs, institution_type it, country co
                                            WHERE i.institution_id = ci.institution_id
                                            AND c.course_id = ci.course_id
                                            AND co.country_id = i.country_id
                                            AND it.institution_type_id = i.institution_type_id
                                            AND cs.course_id = c.course_id
                                            AND cs.student_id =" . $user_id
        );
        return $query->result();
    }

    function my_institutionModel($user_id) {
        $query_str = "SELECT *,CONCAT(street_name_1, ' ', street_name_2, ' ', unit_num, ' ', postal_code) AS institution_address
                                            FROM institution i,institution_has_student si, institution_type it
                                            WHERE i.institution_id = si.institution_id
                                            AND it.institution_type_id = i.institution_type_id
                                            AND si.student_id =?";
        $query = $this->db->query($query_str, $user_id);
        foreach ($query->result() as $row) {
            $sql = $this->db->query("SELECT DISTINCT *
                FROM course c, course_has_institution ci, course_has_student cs
                WHERE c.course_id=ci.course_id
                AND cs.course_id = c.course_id
                AND ci.institution_id = $row->institution_id");
            $sql1 = $sql->result();
        }
        return array(
            'query' => $query->result(),
        );
    }

    //    function selInstitutionModel() {
//        $ids = $this->input->post('institution_id');
//        $sql1 = array();
//        foreach ($ids as $id) {
//            $query = $this->db->query("SELECT * FROM institution WHERE institution_id =$id");
//            $query = $query->result();
//            foreach ($query as $sql) {
//                $query1 = array($sql->institution_name, $sql->institution_id);
//                array_unshift($sql1, $query1);
//            }
//        }
//        return $sql1;
////        }
//    }

    function rmv_insModel($user_id) {
        $institution_id = $this->input->post('institution_id');
        foreach ($institution_id as $id) {
            $query = $this->db->query("DELETE FROM institution_has_student WHERE institution_id=" . $id . " AND student_id= " . $user_id);
        }
        return $query;
    }

    function ins_courseModel($id) {
        //set session user id
        $user_id = 1;
        $query = $this->db->query("SELECT *
                FROM course c, course_has_institution ci, course_has_student cs
                WHERE c.course_id=ci.course_id
                AND cs.course_id = c.course_id
                AND ci.institution_id = $id");
        return $query->result();
    }

}

?>
